package com.whpost.service.dao.cwqf;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.whpost.service.entity.cwqf.OrgCountDTO;
import com.whpost.service.entity.cwqf.TCwqfKhzf;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author YinZhuoyi
 * @since 2025-08-27
 */
public interface TCwqfKhzfMapper extends BaseMapper<TCwqfKhzf> {

    @Select("select * from t_cwqf_khzf where ${ew.sqlSegment}")
    @Results(id="tCwqfKhzfMap",value = {
            @Result(column="zffs_id",property="tCwqfZffs",
                    one=@One(
                            select="com.whpost.service.dao.cwqf.TCwqfZffsMapper.selectById")
            ),
            @Result(column="kh_code",property="tbCrmCustomer",
                    one=@One(
                            select="com.whpost.service.dao.cwqf.TbCrmCustomerMapper.selectByCode")
            ),
            @Result(column="lrry",property="hsCzy",
                    one=@One(
                            select="com.whpost.service.dao.cwqf.HsCzyMapper.selectByCode")
            ),
            @Result(column="lrjg",property="hsJg",
                    one=@One(
                            select="com.whpost.service.dao.cwqf.HsJgMapper.selectById")
            ),
            @Result(column="id",property="wjids",
                    one=@One(
                            select="com.whpost.service.dao.cwqf.TCwqfKhzfFilesMapper.getFilesById")
            ),
            @Result(column="zffs_id",property="zffsId"),
            @Result(column="lrry",property="lrry"),
            @Result(column="lrjg",property="lrjg"),
            @Result(column="id",property="id"),
            @Result(column="kh_code",property="khCode")
    })
    IPage<TCwqfKhzf> getTCwqfKhzfPage(Page<TCwqfKhzf> page, @Param("ew") QueryWrapper wrapper);

    @Select("<script>" +
            "<![CDATA[" +  // 开始包裹纯 SQL 片段（不含动态标签）
            "SELECT " +
            "h.code, " +
            "h.name, " +
            "COUNT(k.lrjg) AS total_count " +
            "FROM hs_jg h " +
            "LEFT JOIN hs_jg sub ON sub.code LIKE h.code || '%' " +
            "LEFT JOIN t_cwqf_khzf k ON k.lrjg = sub.lsh " +
            "AND k.lrrq >= TO_DATE(#{ksrq}, 'yyyy-mm-dd hh24:mi:ss') " +
            "AND k.lrrq <= TO_DATE(#{jsrq}, 'yyyy-mm-dd hh24:mi:ss') " +
            "WHERE h.jb = 2 " +
            "AND h.zybz = '1' " +
            "]]>" +  // 结束包裹纯 SQL 片段
            // 动态标签单独放在 CDATA 外面（避免被忽略）
            "<if test='lshList != null and lshList.size() > 0'> " +
            "AND h.lsh IN " +
            "<foreach collection='lshList' item='lsh' open='(' close=')' separator=','> " +
            "#{lsh} " +
            "</foreach> " +
            "</if> " +
            "<![CDATA[" +  // 继续包裹剩余纯 SQL
            "GROUP BY h.code, h.name " +
            "ORDER BY total_count DESC, h.code ASC" +
            "]]>" +
            "</script>")
    List<OrgCountDTO> statisticOrgData(
            @Param("ksrq") String ksrq,
            @Param("jsrq") String jsrq,
            @Param("lshList") List<Integer> lshList  // 确保参数名与 SQL 中一致
    );

    @Select("SELECT " +
            "h.code, " +
            "h.name, " +
            "COUNT(k.lrjg) AS total_count " +
            "FROM hs_jg h " +
            "LEFT JOIN hs_jg sub ON sub.code LIKE h.code || '%' " + // 关联主机构的所有下级（含自身）
            "LEFT JOIN t_cwqf_khzf k ON k.lrjg = sub.lsh " +       // 业务表通过lsh关联下级机构
            "AND k.lrrq >= TO_DATE(#{ksrq}, 'yyyy-mm-dd hh24:mi:ss') " + // 开始日期筛选
            "AND k.lrrq <= TO_DATE(#{jsrq}, 'yyyy-mm-dd hh24:mi:ss') " + // 结束日期筛选
            "WHERE h.jb = 3 and h.zybz='1'" +                                     // 筛选jb=3的主机构
            "AND h.code LIKE #{orgCode} || '%' " +                  // 主表直接筛选code like orgCode%的数据
            "GROUP BY h.code, h.name " +
            "ORDER BY total_count DESC, h.code ASC")                // 按数量降序，同数量按code升序
    List<OrgCountDTO> statisticOrgDataByCode(
            @Param("orgCode") String orgCode,  // 传入的机构编码前缀
            @Param("ksrq") String ksrq,        // 开始日期（格式：yyyy-mm-dd hh24:mi:ss）
            @Param("jsrq") String jsrq         // 结束日期（格式：yyyy-mm-dd hh24:mi:ss）
    );

}
